package com.java1234jdbc.dao;

import com.java1234jdbc.pojo.Product;
import com.mysql.jdbc.Driver;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class ProductDao {

    //驱动
    private static final String DRIVER_CLASS_NAME = "com.mysql.cj.jdbc.Driver";

    // jdbc:mysql://ip:portname?serverTimezone=Asia/Shanghai
    private static final String DATABASE_URL =
            "jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8";
    private static final String USER_NAME = "root";
    private static final String PASSWORD = "root";

    static {//企业面试题：Class.forName()和ClassLoader有什么区别？
        try {
            //加载并注册mysql jdbc驱动程序类
            //Class.forName(DRIVER_CLASS_NAME);
            DriverManager.registerDriver(new Driver());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //插入数据
    public static int addProduct(Product product) {
        //动态SQL语句(预编译好了)
        String sql = "insert into product(product_name,quantity,create_time)values(?,?,?)";
        Connection connection = null;
        PreparedStatement pstmt = null;

        try {
            connection = DriverManager.getConnection(DATABASE_URL, USER_NAME, PASSWORD);
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, product.getProductName());
            pstmt.setInt(2, product.getQuantity());
            pstmt.setDate(3, DateUtils.asDate(product.getCreateTime()));

            return pstmt.executeUpdate();
//            if (connection.isClosed() == false) {
//                System.out.println("连接数据库成功！");
//            } else {
//                System.out.println("连接数据库失败！");
//            }
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }finally {
            /*if(pstmt!=null){
                try {
                    pstmt.close();
                    pstmt=null;//help gc
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                    connection=null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }*/
            closeAll(null,pstmt,connection);
        }
    }

    private static void closeAll(ResultSet rst,PreparedStatement pstmt,Connection connection){
        if (rst!=null){
            try {
                rst.close();
                rst=null;//let gc do its work
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        if (pstmt!=null){
            try {
                pstmt.close();
                pstmt=null;//let gc do its work
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
                connection=null;
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
    }

    //查询数据
    /*
    查询所有数据
    查询单条语句
     */
public static List<Product> selectList(){
    String sql="SELECT`id`,`product_name`,`quantity`,`create_time`FROM `product`";
    List<Product> productList=new ArrayList<>();
    //2.获得连接对象
    Connection connection=null;
    //声明预编译语句对象
    PreparedStatement pstmt=null;
    //声明一个结果集
    ResultSet rst=null;
    try {
        connection=DriverManager.getConnection(DATABASE_URL,USER_NAME,PASSWORD);
        pstmt=connection.prepareStatement(sql);
        rst=pstmt.executeQuery();
        //int count=0;

        //Product product=new Product();
        while (rst.next()){
            Product product=new Product();
            /*//通过列下标
            //count++;
            int id=rst.getInt(1);
            String name=rst.getString(2);
            int qty=rst.getInt(3);
            //Date create_time=rst.getDate(4);//没有时间戳
            Timestamp create_time=rst.getTimestamp(4);
            System.out.println(id+name+qty+create_time);*/
            //通过列标签
            int id=rst.getInt("id");
            String name=rst.getString("product_name");
            int qty=rst.getInt("quantity");
            Timestamp create_time=rst.getTimestamp("create_time");

            product.setId(id);
            product.setProductName(name);
            product.setQuantity(qty);
            product.setCreateTime(create_time==null?null:create_time.toLocalDateTime());
            //System.out.println(id+name+qty+create_time);
            System.out.println(product.getId()+"\t"+product.getProductName()+"\t"+product.getQuantity()+"\t"+product.getCreateTime());
            productList.add(product);
        }
        //System.out.println("count="+count);
    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        closeAll(rst,pstmt,connection);
    }
    return productList;
}

public static Product selectOne(int pid){
String sql="SELECT `id`,`product_name`,`quantity`,`create_time`FROM`product`where `id`=?";
//声明连接对象
    Connection connection=null;
    //声明预编译命令对象
    PreparedStatement pstmt=null;
    //声明结果集
    ResultSet rst=null;
    Product product=null;
    try {
        connection=DriverManager.getConnection(DATABASE_URL,USER_NAME,PASSWORD);
        pstmt=connection.prepareStatement(sql);
        //填充占位符
        pstmt.setInt(1,pid);
        rst=pstmt.executeQuery();
        if (rst.next()){
            product=new Product();
            //通过列下标读取数据，下标是从1开始，而不是从0开始
            /*int id=rst.getInt(1);
            String name=rst.getString(2);
            int qty=rst.getInt(3);
            Timestamp create_time=rst.getTimestamp(4);*/
            //通过列标签读取数据
            int id=rst.getInt("id");
            String name=rst.getString("product_name");
            int qty=rst.getInt("quantity");
            Timestamp create_time=rst.getTimestamp("create_time");

            product.setId(id);
            product.setProductName(name);
            product.setQuantity(qty);
            product.setCreateTime(create_time==null?null:create_time.toLocalDateTime());

            System.out.println(product.getId()+"\t"+product.getProductName()+"\t"+product.getQuantity()+"\t"+product.getCreateTime());
        }
    }catch (SQLException e){
        e.printStackTrace();
    }finally {
        closeAll(rst,pstmt,connection);
    }
    return product;
}

    public static void main(String[] args) {
        /*Product product = new Product("果粒橙",2000,LocalDateTime.now());
        //addProduct(product);
        int affectedRows = addProduct(product);
        System.out.println("受SQL语句影响的行数是=" + affectedRows);*/
        //selectList();
        Product product=selectOne(3);
        System.out.println(product);

    }
}
